Excecute a query

First you have to create an IDbCommand object with a SQL-Query:


OleDbCommand cmd = new OleDbCommand(String.Format(

                    "select * from {0} where {1}=@lastname",

                    Employee.TableName,

                    Employee.ColumnNames.Lastname));


cmd.Parameters.AddWithValue("@lastname", "Davolio");



Remarks: You should never directly include the names of tables/views/columns in the SQL-statement such as "select * from customers". In case of the name of the customers table changes, the compiler will not recognize this. Instead, use the string constants of SchemaName, TableName and ColumnNames. In this example an OleDbCommand is created to use it with Microsoft Access. You should instead use Command Factory classes which create all the queries your application needs (see Extend a Command Factory).


Then you can pass this IDbCommand object to the static Query method of your generated Data Object class:


IList<Employee> list = Employee.Query(cmd);


The result is a typed IList which contains the result set of the query.



To get a sorted result set, you can pass a sort clause to the Query method:


EmployeeSortClause sortClause = new EmployeeSortClause();

sortClause.AddLastname();

sortClause.AddFirstname();


IList<Employee> list = Employee.Query(cmd, sortClause);


This will return the Employee objects in a (Lastname,Firstname) order.